package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import entity.User;
import exception.DAOException;
import gobang_util.DbUtil;

public class UserDataDAO {
	//用于注册时写数据
	public void Insert(User user) {
		String sql = "insert into user_data(account,password,register_time,score,totall_turn_num,win_times,draw_times,failed_times) values(?,?,now(),?,?,?,?,?)";
		DbUtil.executeSQL(sql,user.getAccount(),user.getPassword(),user.getScore(),user.getTotallTurnNum(),user.getWinTimes(),user.getDrawTimes(),user.getFailedTimes());
	}
	
	public void delete(int id) {
		String sql = "delete from user_data where id=?";
		DbUtil.executeSQL(sql,id);
	}
	
	public void update(User user,int id) {
		String sql = "update user_data set account= ?,password=?,register_time=?,score=?,totall_turn_num=?,win_times=?,draw_times =?,failed_times = ? where id =?";
		DbUtil.executeSQL(sql,user.getAccount(),user.getPassword(),user.getRegisterTime(),user.getScore(),user.getTotallTurnNum(),user.getWinTimes(),user.getDrawTimes(),user.getFailedTimes(),id);
	}
	
	/**
	 * 取回某id的所有数据
	 * @param id
	 * @return
	 */
	public User selectAll(int id) {
		User user = null;
		String sql="select * from user_data where id =?";
		Connection connect = DbUtil.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt = connect.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setAccount(rs.getString("account"));
				user.setPassword(rs.getString("password"));
				user.setRegisterTime(rs.getString("register_time"));
				user.setDrawTimes(rs.getInt("draw_times"));
				user.setScore(rs.getInt("score"));
				user.setWinTimes(rs.getInt("win_times"));
				user.setTotallTurnNum(rs.getInt("totall_turn_num"));
				user.setFailedTimes(rs.getInt("failed_times"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException("SQL异常",e);
		}
		return user;
	}
	
	
	/**
	 * 根据账号和密码查询用户
	 * 
	 * @param account
	 * @param password
	 * @return
	 */
	public User getByAccountAndPwd(String account, String password) {
		User user = null;
		String sql="select * from user_data where account = ? and password = ?";
		Connection connect = DbUtil.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt = connect.prepareStatement(sql);
			pstmt.setString(1, account);
			pstmt.setString(2, password);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setAccount(rs.getString("account"));
				user.setPassword(rs.getString("password"));
				user.setRegisterTime(rs.getString("register_time"));
				user.setDrawTimes(rs.getInt("draw_times"));
				user.setScore(rs.getInt("score"));
				user.setWinTimes(rs.getInt("win_times"));
				user.setTotallTurnNum(rs.getInt("totall_turn_num"));
				user.setFailedTimes(rs.getInt("failed_times"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException("SQL异常",e);
		}finally {
			DbUtil.closeAll(connect,pstmt,rs);
		}
		return user;
	}
	
	/**
	 * 查询指定的账号是否存在
	 * 
	 * @param account
	 * @return 如果存在指定的账号，返回true；否则，返回false
	 */
	public boolean isExist(String account) {
		String sql="select account from user_data";
		Connection connect = DbUtil.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt = connect.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				if(account.equals(rs.getString("account"))) {
					return true;
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * 根据用户的ID修改密码
	 * 
	 * @param id
	 * @param password
	 */
	public void updatePwd(int id, String password) {
		String sql = "update user_data set password = ? where id=?";
		DbUtil.executeSQL(sql,password,id);
	}
}
